package Classes;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;

import Forms.AgentsForm;

/**
 * 
 * @author Dagny Jackson
 * General Agents Class creation
 * everything on this page created by Dagny
 * EXCEPT agentsVector() function.
 */
public class Agents 
{
	//===============================Properties=======================================
	// start Dagny Jackson properties
	private int agentId;
	private String agentFirstName;
	private String agentMiddleInitial;
	private String agentLastName;
	private String agentBusPhone;
	private String agentEmail;
	private String agentPosition;
	private int agencyID;
	// end Dagny Jackson properties
	
	//===============================Constructors=======================================
	
	//===============================Getters and Setters================================
	// start Dagny Jackson getters and setters
	public int getAgentId() 
	{
		return agentId;
	}
	public void setAgentId(int agentId) 
	{
		this.agentId = agentId;
	}
	
	
	public String getAgentFirstName() 
	{
		return agentFirstName;
	}
	public void setAgentFirstName(String agentFirstName) 
	{
		this.agentFirstName = agentFirstName;
	}
	
	
	public String getAgentMiddleInitial() 
	{
		return agentMiddleInitial;
	}
	public void setAgentMiddleInitial(String agentMiddleInitial) 
	{
		this.agentMiddleInitial = agentMiddleInitial;
	}
	
	
	public String getAgentLastName() 
	{
		return agentLastName;
	}
	public void setAgentLastName(String agentLastName) 
	{
		this.agentLastName = agentLastName;
	}
	
	
	public String getAgentBusPhone() 
	{
		return agentBusPhone;
	}
	public void setAgentBusPhone(String agentBusPhone) 
	{
		this.agentBusPhone = agentBusPhone;
	}
	
	
	public String getAgentEmail() 
	{
		return agentEmail;
	}
	public void setAgentEmail(String agentEmail) 
	{
		this.agentEmail = agentEmail;
	}
	
	
	public String getAgentPosition() 
	{
		return agentPosition;
	}
	public void setAgentPosition(String agentPosition) 
	{
		this.agentPosition = agentPosition;
	}
	
	
	public int getAgencyID() 
	{
		return agencyID;
	}
	public void setAgencyID(int agencyID) 
	{
		this.agencyID = agencyID;
	}
	// end Dagny Jackson getters and setters
	
	//===============================Override To String Method==========================
	//start Dagny Jackson override

	@Override
	public String toString() 
	{
	    String result = agentLastName + ", " + agentFirstName;
	    return result;
	}
	
	//end Dagny Jackson override
	
	//===============================Methods============================================
	
	//start Dagny Jackson methods
	
	//this method connects to the DB
	public Connection connectToDb()
	{
		try 
		{	
			//load the driver for oracle
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			//establish a connection to the database
			//first string is location of the database (url)
			//second string is user name
			//third string is password
			Connection agentConnection =  DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g", "ictoosd", "ictoosd");
						
			//String defaultSqlQuery = "alter session set NLS_COMP=LINGUISTIC";
			
			
			return agentConnection;
		} 
		catch (ClassNotFoundException e) 
		{
			e.printStackTrace();
			Connection noConnectionMade = null;
			return noConnectionMade;
		} catch (SQLException sqle) 
		{
			sqle.printStackTrace();
			Connection noConnectionMade = null;
			return noConnectionMade;
		}	
	}
	
	//this function returns Agent ArrayLists of requested Agents in the database
	//based on query string passed through
	public ArrayList<Agents> agentsAr(String sqlQuery)
	{
		ArrayList<Agents> agentsArrayList = new ArrayList<Agents>();

		Connection listConn = connectToDb();
		if (listConn != null)
		{
			try {
				Statement stmnt = listConn.createStatement();
				//Call the statement object
				//executeQuery - SELECT statements...brings back data results
				//executeUpdate - UPDATE, INSERT, DELETE ...when modifying data, adding tables, dropping them, etc.
				ResultSet rs = stmnt.executeQuery(sqlQuery); //"SELECT * FROM agents ORDER BY AgtLastName"
	
				while (rs.next())
				{
					//get the item and put it into a new Agent
					Agents anAgent = new Agents();
					anAgent.setAgentId(Integer.parseInt(rs.getString(1)));
					anAgent.setAgentFirstName(rs.getString(2));
					anAgent.setAgentMiddleInitial(rs.getString(3));
					anAgent.setAgentLastName(rs.getString(4));
					anAgent.setAgentBusPhone(rs.getString(5));
					anAgent.setAgentEmail(rs.getString(6));
					anAgent.setAgentPosition(rs.getString(7));
					anAgent.setAgencyID(Integer.parseInt(rs.getString(8)));
					
					//add agent to the 
					agentsArrayList.add(anAgent);
				}
				listConn.close();
			} 
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			return agentsArrayList;
		}
		else
		{
			System.out.println("No SQL connection was made.");
			return agentsArrayList;
		}
	}
	
	//added by: Karla Barber
	//this function returns Agent ArrayLists of requested Agents in the database
	//based on query string passed through
	public Vector<Object> agentsVector(String sqlQuery)
	{
		Vector<Object> agentsVector = new Vector<Object>();

		Connection listConn = connectToDb();
		if (listConn != null)
		{
			try {
				Statement stmnt = listConn.createStatement();
				//Call the statement object
				//executeQuery - SELECT statements...brings back data results
				//executeUpdate - UPDATE, INSERT, DELETE ...when modifying data, adding tables, dropping them, etc.
				ResultSet rs = stmnt.executeQuery(sqlQuery); //"SELECT * FROM agents ORDER BY AgtLastName"

				agentsVector.addElement("Select an Agent...");
				while (rs.next())
				{
					//get the item and put it into a new Agent
					Agents anAgent = new Agents();
					anAgent.setAgentId(Integer.parseInt(rs.getString(1)));
					anAgent.setAgentFirstName(rs.getString(2));
					anAgent.setAgentMiddleInitial(rs.getString(3));
					anAgent.setAgentLastName(rs.getString(4));
					anAgent.setAgentBusPhone(rs.getString(5));
					anAgent.setAgentEmail(rs.getString(6));
					anAgent.setAgentPosition(rs.getString(7));
					anAgent.setAgencyID(Integer.parseInt(rs.getString(8)));
					
					//add agent to the 
					agentsVector.addElement(anAgent);
				}
				listConn.close();
			} 
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			return agentsVector;
		}
		else
		{
			System.out.println("No SQL connection was made.");
			return agentsVector;
		}
	}

	
	//this function returns an Agent Array from any ArrayList of Agents
	public Agents[] createAgentArray(ArrayList<Agents> agentsAr)
	{
		int lengthOfAgentsArrayList = agentsAr.size();
		Agents [] allAgents = new Agents[lengthOfAgentsArrayList];
		for (int a = 0; a <lengthOfAgentsArrayList; a++)
		{
			allAgents[a] = agentsAr.get(a);
		}
		return allAgents;
	}
	
	//this function returns the next sequential id for a new agent added to the database
	public int getNextNewAgentId()
	{
		int newAgentId = 0;
		
		//connect to database
		Connection getConn = connectToDb();
		
		if (getConn != null)
		{
			try {
				//Create a new Statement
				Statement stmnt = getConn.createStatement();
				//Call the statement object
				//executeQuery - SELECT statements...brings back data results
				//executeUpdate - UPDATE, INSERT, DELETE ...when modifying data, adding tables, dropping them, etc.
				ResultSet rs = stmnt.executeQuery("select max(agentid) from ictoosd.agents");
				//SELECT TOP 1 AgentId FROM agents ORDER BY AgentId DESC
				
				
				while (rs.next())
				{
					//grab the last number and add one to it to get next number
					newAgentId = rs.getInt(1) + 1;
				}
				//test by printing out the array list
				//System.out.println(agentIdAr);
				getConn.close();
			}
			catch (SQLException e) 
			{
				e.printStackTrace();
			}

		}
		else
		{
			System.out.println("No SQL connection was made.");
		}
		
		return newAgentId;
	}
	
	//this method will take a newly added Agent and add their information to the
	//agent login table in the database.
	public void addAgentLogin(String username, int agentid, String password)
	{
		Connection addConn = connectToDb();
		if (addConn != null)
		{
			try 
			{
				String addString = "INSERT INTO agentlogin " +
				"(username, agentid, password) " + 
				"Values(?,?,?)";
	        
		        PreparedStatement addStmnt = addConn.prepareStatement(addString);
		       
		        addStmnt.setString(1, username);
		        addStmnt.setInt(2, agentid);
		        addStmnt.setString(3, password);
		        
		        int rowsChanged = addStmnt.executeUpdate();
		        
		        if (rowsChanged == 0)
		        {
		        	System.out.println("update failed");
		        }
		        else
		        {
		        	System.out.println("updated " + rowsChanged + " row(s)");
		        }
		        addConn.commit();
		        addConn.close();
				
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		else
		{
			System.out.println("No SQL connection was made.");
		}
	}
	
	//this method passes all the information from the fields
	//in the form and adds a brand new agent
	public void addAgent(int agentId, String first, String middle, String last, String phone, String email, String position, int agencyId)
	{
		Connection addConn = connectToDb();
		if (addConn != null)
		{
			try 
			{
				String addString = "INSERT INTO agents " +
				"(AgentId, AgtFirstName, AgtMiddleInitial, AgtLastName, " +
				"AgtBusPhone, AgtEmail, AgtPosition, AgencyId) " + 
				"Values(?,?,?,?,?,?,?,?)";
	        
		        PreparedStatement addStmnt = addConn.prepareStatement(addString);
		       
		        addStmnt.setInt(1, agentId);
		        addStmnt.setString(2, first);
		        addStmnt.setString(3, middle);
		        addStmnt.setString(4, last);
		        addStmnt.setString(5, phone);
		        addStmnt.setString(6, email);
		        addStmnt.setString(7, position);
		        addStmnt.setInt(8, agencyId);
		        
		        int rowsChanged = addStmnt.executeUpdate();
		        
		        if (rowsChanged == 0)
		        {
		        	System.out.println("update failed");
		        }
		        else
		        {
		        	System.out.println("updated " + rowsChanged + " row(s)");
		        }
		        addConn.commit();
		        addConn.close();
				
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		else
		{
			System.out.println("No SQL connection was made.");
		}
	}
	
	//this method passes all the information from the fields
	//in the form and updates the selected agent
	public void updateAgent(int agentId, String first, String middle, String last, String phone, String email, String position, int agencyId)
	{
		Connection updateConn = connectToDb();
		if (updateConn != null)
		{
			try 
			{
				String updateString = "UPDATE agents set "
		           	+ "agtfirstname=?, agtmiddleinitial=?, agtlastname=?, "
		            + "agtbusphone=?, agtemail=?, "
		            + "agtposition=?, agencyid=? "
		            + " WHERE agentid=?";
		        
		        
		        PreparedStatement updateStmnt = updateConn.prepareStatement(updateString);
		       
		        updateStmnt.setString(1, first);
		        updateStmnt.setString(2, middle);
		        updateStmnt.setString(3, last);
		        updateStmnt.setString(4, phone);
		        updateStmnt.setString(5, email);
		        updateStmnt.setString(6, position);
		        updateStmnt.setInt(7, agencyId);
		        updateStmnt.setInt(8, agentId);
		        
		        int rowsChanged = updateStmnt.executeUpdate();
		        
		        if (rowsChanged == 0)
		        {
		        	System.out.println("update failed");
		        }
		        else
		        {
		        	System.out.println("updated " + rowsChanged + " row(s)");
		        }
		        updateConn.commit();
		        updateConn.close();
				
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		else
		{
			System.out.println("No SQL connection was made.");
		}
	}

	//method to delete any agentlogin record with an id of the agent passed through
	//use in conjunction with the deleteAgent method
	public void deleteAgentLogin(int agentid)
	{
		Connection deleteConn = connectToDb();
		if (deleteConn != null)
		{
			try 
			{
				String deleteString = "DELETE FROM agentlogin WHERE agentid=" + agentid;
	        
		        PreparedStatement deleteStmnt = deleteConn.prepareStatement(deleteString);
		       		        
		        int rowsChanged = deleteStmnt.executeUpdate();
		        
		        if (rowsChanged == 0)
		        {
		        	System.out.println("update failed");
		        }
		        else
		        {
		        	System.out.println("updated " + rowsChanged + " row(s)");
		        }
		        deleteConn.commit();
		        deleteConn.close();
				
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		else
		{
			System.out.println("No SQL connection was made.");
		}
	}
	
	//method to change any matching agentIDs (of the agent id passed through) in customer table to null
	//use in conjunction with the deleteAgent method
	public void clearAgentIdInCustomerTable(int agentid)
	{
		Connection clearConn = connectToDb();
		if (clearConn != null)
		{
			try 
			{
				String clearString = "UPDATE customers SET agentid=null WHERE agentid=" + agentid;
	        
		        PreparedStatement clearStmnt = clearConn.prepareStatement(clearString);
		       		        
		        int rowsChanged = clearStmnt.executeUpdate();
		        
		        if (rowsChanged == 0)
		        {
		        	System.out.println("update failed");
		        }
		        else
		        {
		        	System.out.println("updated " + rowsChanged + " row(s)");
		        }
		        clearConn.commit();
		        clearConn.close();
				
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		else
		{
			System.out.println("No SQL connection was made.");
		}
	}
	
	//this method deletes the selected agent
	public void deleteAgent(int agentId)
	{
		//DELETE FROM ictoosd.agents WHERE agentid = 50
		Connection deleteConn = connectToDb();
		if (deleteConn != null)
		{
			try 
			{
				String deleteString = "DELETE FROM agents WHERE agentid=?";
		        
		        
		        PreparedStatement deleteStmnt = deleteConn.prepareStatement(deleteString);
		       
		        deleteStmnt.setInt(1, agentId);
		        
		        int rowsChanged = deleteStmnt.executeUpdate();
		        
		        if (rowsChanged == 0)
		        {
		        	System.out.println("update failed");
		        }
		        else
		        {
		        	System.out.println("updated " + rowsChanged + " row(s)");
		        }
		        deleteConn.commit();
		        deleteConn.close();
				
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		else
		{
			System.out.println("No SQL connection was made.");
		}
	}

	//this function returns a listing of all the agent positions in the database
	public ArrayList<String> loadPositions()
	{
		//create an ArrayList to hold the agent ids
		ArrayList<String> positionAr = new ArrayList<String>();
		positionAr.add("- - Select a Position - -");
		
		//connect to database
		Connection loadConn = connectToDb();
		
		if (loadConn != null)
		{
			try {
				//Create a new Statement
				Statement stmnt = loadConn.createStatement();
				//Call the statement object
				//executeQuery - SELECT statements...brings back data results
				//executeUpdate - UPDATE, INSERT, DELETE ...when modifying data, adding tables, dropping them, etc.
				ResultSet rs = stmnt.executeQuery("SELECT DISTINCT agtposition FROM agents ORDER BY agtposition");
				
				
				while (rs.next())
				{
					//get the item and put it into the ArrayList
					positionAr.add(rs.getString(1));
				}
				//test by printing out the array list
				//System.out.println(agentIdAr);
				loadConn.close();
			}
			catch (SQLException e) 
			{
				e.printStackTrace();
			}

		}
		else
		{
			System.out.println("No SQL connection was made.");
		}
		return positionAr;
	}

	//this function returns a listing of all the agencies in the database
	public ArrayList<String> loadAgencies()
	{
		//create an ArrayList to hold the agent ids
		ArrayList<String> agencyAr = new ArrayList<String>();
		agencyAr.add("--Select an Agency--");
		
		//connect to database
		Connection loadConn = connectToDb();
		
		if (loadConn != null)
		{
			try {
				//Create a new Statement
				Statement stmnt = loadConn.createStatement();
				//Call the statement object
				//executeQuery - SELECT statements...brings back data results
				//executeUpdate - UPDATE, INSERT, DELETE ...when modifying data, adding tables, dropping them, etc.
				ResultSet rs = stmnt.executeQuery("SELECT DISTINCT agncycity FROM agencies ORDER BY agncycity");
				
				
				while (rs.next())
				{
					//get the item and put it into the ArrayList
					agencyAr.add(rs.getString(1));
				}
				//test by printing out the array list
				//System.out.println(agentIdAr);
				loadConn.close();
			}
			catch (SQLException e) 
			{
				e.printStackTrace();
			}

		}
		else
		{
			System.out.println("No SQL connection was made.");
		}
		return agencyAr;
	}

	//this function returns the specific information of a chosen agent
	public ArrayList<String> loadAgentInformation(int agentid)
	{
		ArrayList<String> agentInfoAr = new ArrayList<String>();

		Connection loadConn = connectToDb();
		if (loadConn != null)
		{
			try 
			{
				//Create a new Statement
				Statement stmnt = loadConn.createStatement();
				//create a reference to the AgentFrame
				
				//Call the statement object
				ResultSet rs = stmnt.executeQuery("SELECT * FROM agents WHERE agentid=" + agentid);
				
				//while there is a next row in the result set
				while (rs.next())
				{
					//process the columns in each row
					//this is NOT A zero  based index. Starts with 1!
					for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) 
					{
						agentInfoAr.add(rs.getString(i));
					}
				}
				loadConn.close();
				
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		else
		{
			System.out.println("No SQL connection was made.");
		}
	return agentInfoAr;
	}
	//end Dagny Jackson methods
}
